<?php
namespace framework;
// $obj = Mysql::setLink();



//$result = $obj -> where('uid = 1') -> fields('uid,username') -> select();
//$result = $obj -> insert(['name' => 'bilibili','url' => 'www.bilibili.com','addtime' => time(),'aaa' => 'fea'],true);
//$result = $obj -> where(['name = "bilibili"','displayorder = 0']) -> order(['id desc','name asc']) -> limit('1,2') -> update(['logo' => 3]);
//$result = $obj -> where('name = "bilibili"') -> order('lid desc') -> limit(1) -> delete();
// $result = $obj -> where(['name = 我的领地','lid >= 3']) -> count();
// var_dump($result);

class Mysql {


//---------------------------定义属性------------------------------
	//单例属性
	static protected $obj = null;
	//数据库连接属性
	protected $link;
	protected $host;
	protected $user;
	protected $pwd;
	protected $charset;
	protected $dbName;
	//表前缀
	protected $prefix;
	//表名
	public $tableName;
	//用于缓存表里所有的字段
	protected $fields;
	//缓存文件路径
	protected $cache; 


	//数据库操作条件
	protected $options;
	//存储上一次的SQL语句
	public $sql;


//---------------------------单例静态方法------------------------------
	static function setLink($table=null,$info=null) {
		if (is_null(static::$obj)) {
			static::$obj = new static($table,$info);
		}
		return static::$obj;
	}






//---------------------------构造方法------------------------------
	 function __construct($table=null,$info=null) {
	 	if (empty($info)) {
	 		$info = include('./config/database.php');
	 	}

	 	$this -> host = $info['DB_HOST'];
	 	$this -> user = $info['DB_USER'];
	 	$this -> pwd = $info['DB_PWD'];
	 	$this -> charset = $info['DB_CHARSET'];
	 	$this -> dbName = $info['DB_NAME'];
	 	$this -> prefix = $info['DB_PREFIX'];
	 	//连接数据库
	 	$this -> link = $this -> connect();
	 	$this -> dbSelect();
	 	$this -> setCharset();
	 	//获得表名
	 	if (!empty($table)) {
	 		$len = strlen($this -> prefix);
	 		if (strncmp($this -> prefix,$table,$len)) {
	 			$this -> tableName = $this -> prefix . $table;
	 		} else {
	 			$this -> tableName = $table;
	 		}
	 		
	 	}
	 	$this -> tableName = $this -> getTableName();
	 	//获得表的所有字段
	 	$this -> cache = $this -> checkCache($info['DB_CACHE']);
	 	$this -> fields = $this -> getFields();
	 	//初始化条件
	 	$this -> options = $this -> options();
	 }


//---------------------------魔术方法------------------------------
	//调用无法访问的方法时
	function __call($func,$arg) {
	//SQL语句条件处理
		$arr = ['where','order','limit','group','having'];
		if (in_array($func,$arr)) {
			return $this -> condition($func,$arg[0]);
		}

	//自定义单条件查询 getBy字段()
		if (strncmp($func,'getBy',5) == 0) {
			$field = substr($func,5);
			return $this -> getBy($field,$arg[0]);
		}
	//自定义统计查询
		$arr2 = ['count','max','min','avg','sum'];
		if (in_array($func,$arr2)) {
			$field = (empty($arg) ? null : $arg[0]);
			return $this -> calc($func,$field);
		}
		return false;
	}
//---------------------------数据库链接------------------------------
	 //连接数据库
	 protected function connect() {
	 	$link = mysqli_connect($this -> host,$this -> user,$this -> pwd);
	 	if (!$link) {
	 		exit('数据库连接失败！');
	 	}
	 	return $link;
	 }
	 //选择数据库
	 protected function dbSelect() {
	 	if (!mysqli_select_db($this -> link,$this -> dbName)) {
	 		mysqli_close($this -> link);
			return false;
	 	}
	 	return true;
	 }
	 //设置字符集
	 protected function setCharset() {
	 	if (!mysqli_set_charset($this -> link,$this -> charset)) {
	 		mysqli_close($this -> link);
			return false;
	 	}
	 	return true;
	 }



	 






//---------------------------表名和字段名初始化------------------------------
	//获得表名
	protected function getTableName() {
		if (!empty($this -> tableName)) {
			return $this -> tableName;
		}
		//从类名中提取
		$className = get_class($this);
		$M_pos = strrpos($className,'M');
		$className = substr($className,0,$M_pos);
		$pos = strrpos($className,'\\');
		return $this -> prefix . str_replace('Model','',substr($className,$pos+1));
	}
	//获得字段
	protected function getFields() {
		//拼接缓存路径
		$cacheFile = $this -> cache . $this -> tableName . '.php';
		if (file_exists($cacheFile)) {
			return include($cacheFile);	
		} 
		//查询所有字段
		$sql = 'desc ' . $this -> tableName;
		//$result = $this -> selectQuery($sql);
		$result = mysqli_query($this -> link,$sql);
		//将字段存入数组
		$arr = [];
		foreach ($result as $key => $value) {
			if ($value['Key'] == 'PRI') {
				$arr['_pk'] = $value['Field'];
			}
			$arr[] = $value['Field'];
		}
		//写入缓存文件
		$content = "<?php \n return " . var_export($arr,true) . ';';
		file_put_contents($cacheFile,$content);
		return $arr;
	}
	//检查缓存文件夹 如果不存在就创建一个
	protected function checkCache($dir) {
		$dir = rtrim($dir,'/') . '/';
		if (!is_dir($dir)) {
			mkdir($dir,0777,true);
		}
		if (!is_writable($dir) || !is_readable($dir)) {
			chmod($dir,0777);
		}
		return $dir;
	}
	



//---------------------------数据库操作------------------------------
	//插入
	function insert(array $data,$insertId=false) {				//$data必须为数组

		$sql = 'INSERT INTO %TABLE%(%FIELDS%) VALUES(%VALUES%)';
		$data = $this -> addQuotes($data);
		$data = $this -> checkKeys($data);
		$this -> options['fields'] = join(',',array_keys($data));
		$this -> options['values'] = join(',',$data);
		$sql = str_replace([
								'%FIELDS%',
								'%VALUES%',
								'%TABLE%',
						],
						   [
						   		$this -> options['fields'],
						   		$this -> options['values'],
						   		$this -> options['table']
						],
						$sql);
		return $this -> query($sql,$insertId);
	}
	//更改
	function update($data) {				//$data必须为数组或字符串

		if (empty($this->options['where'])) {
			exit('更新数据时请务必写上条件');
		}
		$sql = 'UPDATE %TABLE% SET %SET% %WHERE% %ORDER% %LIMIT%';
		if (is_array($data)) {
			$data = $this -> addQuotes($data);
			$data = $this -> checkKeys($data);
			$data = $this -> dealSet($data);
		}
		$this -> options['set'] = $data;
		$sql = str_replace([
								'%SET%',
								'%WHERE%',
								'%ORDER%',
								'%LIMIT%',
								'%TABLE%',
						],
						   [
						   		$this -> options['set'],
						   		$this -> options['where'],
						   		$this -> options['order'],
						   		$this -> options['limit'],
						   		$this -> options['table']
						],
						$sql);
		return $this -> query($sql);
	}
	//删除
	function delete() {
		if (empty($this->options['where'])) {
			exit('删除数据时请务必写上条件');
		}
		$sql = 'DELETE FROM %TABLE% %WHERE% %ORDER% %LIMIT%';
		$sql = str_replace([
								'%WHERE%',
								'%ORDER%',
								'%LIMIT%',
								'%TABLE%',
						],
						   [
						   		$this -> options['where'],
						   		$this -> options['order'],
						   		$this -> options['limit'],
						   		$this -> options['table']
						],
						$sql);
		return $this -> query($sql);
	}
	//查询 1-完全查询
	function select($resultType = MYSQLI_BOTH) {
		$sql = 'SELECT %FIELDS% FROM %TABLE% %WHERE% %ORDER% %GROUP% %HAVING% %LIMIT%';
		$sql = str_replace([
								'%FIELDS%',
								'%WHERE%',
								'%ORDER%',
								'%GROUP%',
								'%HAVING%',
								'%LIMIT%',
								'%TABLE%'
						],
						   [
						   		$this -> options['fields'],
						   		$this -> options['where'],
						   		$this -> options['order'],
						   		$this -> options['group'],
						   		$this -> options['having'],
						   		$this -> options['limit'],
						   		$this -> options['table']
						],
						$sql);
		return $this -> selectQuery($sql,$resultType);
	}
	//查询 2-单条件查询
	function getBy($field,$arg) {
		if (is_string($arg)) {
			$arg = '\'' . $arg . '\'';
		}
		$where = $field . ' = ' . $arg;
		return $this -> where($where) -> select(MYSQLI_ASSOC);
	}
	//查询 3-统计查询
	function calc($func,$arg=null) {
		if (empty($arg)) {
			$arg = $this -> fields['_pk'];
		}
		$this -> options['fields'] = "$func($arg)";
		return $this -> select();
	}

	//执行SQL语句-->查询用									//可以用于自定义SQL语句查询
	function selectQuery($sql,$type=MYSQLI_BOTH) {
		//存储SQL语句
		$this -> sql = $sql;
		//每次存完SQL语句都要初始化条件
		$this -> options = $this -> options();
		//执行
		$result = mysqli_query($this -> link,$sql);
		if (!$result) {
			return false;
		}
		return mysqli_fetch_all($result,$type);
	}
	//执行SQL语句-->增删改用
	function query($sql,$insertId=false) {
		//存储SQL语句
		$this -> sql = $sql;
		//每次存完SQL语句都要初始化条件
		$this -> options = $this -> options();
		//执行
		$result = mysqli_query($this -> link,$sql);
		if ($result && mysqli_affected_rows($this -> link)) {
			if ($insertId) {
				return mysqli_insert_id($this->link);
			} else {
				return true;
			}
		}
		return false;
	}


//---------------------------条件处理------------------------------
	//条件初始化
	protected function options() {
		$fields = join(',',array_unique($this -> fields));
		return [
								'where' => '',
								'limit' => '',
								'order' => '',
								'having' => '',
								'group' => '',
								'fields' => $fields,
								'table' => $this -> tableName
						];
	}
	//条件处理
	protected function condition($func,$arg) {
		$join = (in_array($func,['where','having']) ? ' and ' : ',' );
		$keyWord = (in_array($func,['order','group']) ? $func . ' by ' : $func);

			if (is_string($arg) || is_numeric($arg)) {
				$this -> options[$func] = ' ' . $keyWord . ' ' . $arg;
			} else if (is_array($arg)) {
				if ($func == 'where') {
					$arg = $this -> dealWhere($arg);
				}
				$this -> options[$func] = ' ' . $keyWord . ' ' . join($join,$arg);
			} else {
				return false;
			}
			return $this;
	}
	//设置表名
	public function table($table)
	{
		if (is_string($table)) {
			$table = explode(',', $table);
		} 
		if (is_array($table)) {
			$len = strlen($this -> prefix);
			foreach ($table as $key => $value) {
				if (strncmp($value,$this -> prefix, $len)) {
					$value = $this -> prefix . $value;
				}
				$table[$key] = $value;			
			}
			$this -> options['table'] = join(',',$table);
		}
		return $this;
	}
	//字段条件
	function fields($fields) {
		if (is_string($fields)) {
			$this -> options['fields'] = $fields;
		} else if (is_array($fields)) {
			$this -> options['fields'] = join(',',$fields);
		}
		return $this;
	}
	//插入和更新时的字符串参数拼接引号
	protected function addQuotes(array $data) {
		foreach ($data as $key => $value) {
			if (is_string($value)) {
				$data[$key] = '\'' . $value . '\'';
			}
		}
		return $data;
	}
	//插入和更新时 去掉不存在的字段
	protected function checkKeys(array $data) {
		$fields = array_flip($this -> fields);
		$data = array_intersect_key($data,$fields);
		return $data;
	}
	//处理更新数据
	protected function dealSet(array $data) {
		$set = '';
		foreach ($data as $key => $value) {
			$set .= $key . ' = ' . $value . ',';
		}
		return rtrim($set,',');
	}
	//给where条件里的字符串拼接引号
	protected function dealWhere(array $arr) {
		$reg = '/([<>!=]+\s*)(.+)/';
		foreach ($arr as $key => $value) {
			preg_match($reg,$value,$match);
			$match[2] = trim($match[2],'\'"');
			$replace = $match[1] . '\'' . $match[2] . '\'';
			$arr[$key] = str_replace($match[0],$replace,$value);
		}
		return $arr;
	}



//---------------------------构造函数------------------------------
}